﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
Imports System.Collections.Generic

Public Class UserDAO
    Public Shared _TableName As String = "tblUser"

    Public Shared FIELDS As String = "U.ID AS UID, U.FullName AS UFullName, U.Username AS UUsername, U.Password AS UPassword, U.UserTypeID AS UUserTypeID, " & _
        " U.IsActive AS UIsActive, U.IsLogin AS UIsLogin, U.CreatedDate AS UCreatedDate, U.ActivationCode AS UActivationCode, U.ShippingAddress AS UShippingAddress"

    Public Shared Function GetUsers() As List(Of User)
        Dim strSQL As String = "SELECT  * FROM " & _TableName & " WHERE 1=1 "
        Dim users As List(Of User) = Nothing
        Dim m_Conn As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Try
            m_Conn = DBHelper.GetConnection()
            reader = SqlHelper.ExecuteReader(m_Conn, CommandType.Text, strSQL)
            If reader IsNot Nothing Then
                users = New List(Of User)()
                While reader.Read
                    Dim strValue As New ArrayList()
                    strValue = DBHelper.ReadValue(reader)
                    Dim _rowObj As User = New User()
                    Dim indexStart As Integer = 0
                    _rowObj = GetEntity(indexStart, strValue)
                    users.Add(_rowObj)
                End While
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            If m_Conn IsNot Nothing Then
                m_Conn.Close()
                m_Conn.Dispose()
                m_Conn = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try

        Return users

    End Function

    Protected Shared Function Insert(ByVal obj As User, ByVal iAutoKey As Boolean, ByVal bPassEncrypted As Boolean, ByRef con As SqlConnection, ByRef trans As SqlTransaction) As Boolean
        If obj Is Nothing Then
            Return False
        End If
        If iAutoKey Then
            obj.ID = System.Guid.NewGuid.ToString()
        End If
        Try
            Dim sql As String = "INSERT INTO " & _TableName & "(ID ,FullName, UserName, Password, UserTypeID ,IsActive, IsLogin, ActivationCode, ShippingAddress) "
            sql &= " VALUES(@ID ,@FullName, @UserName, "
            sql &= IIf(Not bPassEncrypted, "HASHBYTES('MD5', '" & obj.Password & "'), ", "@Password, ")
            sql &= "@UserTypeID ,@IsActive, @IsLogin, @ActivationCode, @ShippingAddress);"

            Dim lParam As List(Of SqlParameter) = New List(Of SqlParameter)
            lParam.Add(New SqlParameter("@ID", obj.ID))
            lParam.Add(New SqlParameter("@FullName", obj.FullName))
            lParam.Add(New SqlParameter("@UserName", obj.UserName))

            If bPassEncrypted Then
                lParam.Add(New SqlParameter("@Password", obj.Password))
            End If

            lParam.Add(New SqlParameter("@UserTypeID", obj.UserTypeID))
            lParam.Add(New SqlParameter("@IsActive", obj.IsActive))
            lParam.Add(New SqlParameter("@IsLogin", obj.IsLogin))
            lParam.Add(New SqlParameter("@ActivationCode", obj.ActivationCode))
            lParam.Add(New SqlParameter("@ShippingAddress", obj.ShippingAddress))

            Dim lParamArray As SqlParameter() = lParam.ToArray()

            SqlHelper.ExecuteScalar(trans, CommandType.Text, sql, lParamArray)

            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
        End Try
        Return False
    End Function

    Protected Shared Function Update(ByVal obj As User, ByVal bPassEncrypted As Boolean, ByRef con As SqlConnection, ByRef trans As SqlTransaction) As Boolean
        If obj Is Nothing Then
            Return False
        End If
        Try
            Dim sql As String = ""
            sql = "UPDATE " & _TableName & " " & _
                  "SET FullName=@FullName, UserName=@Username, "
            sql &= IIf(Not bPassEncrypted, "Password=HASHBYTES('MD5', '" & obj.Password & "'), ", "Password=@Password, ")
            sql &= "UserTypeID=@UserTypeID, IsActive=@IsActive, IsLogin=@IsLogin, ActivationCode=@ActivationCode, ShippingAddress=@ShippingAddress " & _
                   "WHERE ID=@ID "

            Dim lParam As List(Of SqlParameter) = New List(Of SqlParameter)
            lParam.Add(New SqlParameter("@ID", obj.ID))
            lParam.Add(New SqlParameter("@FullName", obj.FullName))
            lParam.Add(New SqlParameter("@UserName", obj.UserName))
            If bPassEncrypted Then
                lParam.Add(New SqlParameter("@Password", obj.Password))
            End If
            lParam.Add(New SqlParameter("@UserTypeID", obj.UserTypeID))
            lParam.Add(New SqlParameter("@IsActive", obj.IsActive))
            lParam.Add(New SqlParameter("@IsLogin", obj.IsLogin))
            lParam.Add(New SqlParameter("@ActivationCode", obj.ActivationCode))
            lParam.Add(New SqlParameter("@ShippingAddress", obj.ShippingAddress))

            Dim lParamArray As SqlParameter() = lParam.ToArray()

            SqlHelper.ExecuteScalar(trans, CommandType.Text, sql, lParamArray)
            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally

        End Try

        Return False

    End Function


    Protected Shared Function ChangePassword(ByVal obj As User, ByRef con As SqlConnection, ByRef trans As SqlTransaction) As Boolean
        Return Update(obj, False, con, trans)
    End Function

    Protected Shared Function DeleteUser(ByVal _ID As String, ByRef con As SqlConnection, ByRef trans As SqlTransaction) As Boolean
        Try
            'Dim sql As String = "UPDATE " & _TableName & " SET deleted=1 " & _
            '                    "WHERE ID=@ID"

            Dim sql As String = "DELETE FROM " & _TableName & " " & _
                                "WHERE ID=@ID "

            Dim lParamArray As SqlParameter() = Nothing
            lParamArray = New SqlParameter() { _
                New SqlParameter("@ID", _ID) _
            }
            'SqlHelper.ExecuteScalar(trans, CommandType.Text, sql, lParamArray)

            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, lParamArray)
            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
        End Try
        Return False
    End Function

    Public Shared Function Exists(ByVal _ID As String, ByVal _UserName As String) As Boolean
        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Dim index As Integer = 0
        Dim lParamArray As SqlParameter() = New SqlParameter(3) {}
        Dim sql As String = "SELECT * FROM " & _TableName & " " & _
                            "WHERE 1=1 "
        If _UserName <> "" Then
            lParamArray(index) = New SqlParameter("@UserName", _UserName)
            sql &= "AND UserName=@UserName "
            index = index + 1
        End If
        If _ID <> "" Then
            sql &= "AND ID <> @ID "
            lParamArray(index) = New SqlParameter("@ID", _ID)
            index = index + 1
        End If
        Try
            con = DBHelper.GetConnection()
            reader = SqlHelper.ExecuteReader(con, CommandType.Text, sql, lParamArray)
            If reader.Read Then
                Dim strValue As New ArrayList()
                strValue = DBHelper.ReadValue(reader)
                Return True
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
                con = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try
        Return False
    End Function



    Public Shared Function AuthorizedUser(ByVal _Username As String, ByVal _Password As String) As User
        If _Username Is Nothing Or _Password Is Nothing Then Return Nothing
        If _Username.Trim = "" Or _Password.Trim = "" Then Return Nothing

        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Dim sql As String = "SELECT * FROM " & _TableName & " " & _
                            "WHERE UserName=@UserName AND Password=HASHBYTES('MD5', '" & _Password.Trim.Replace("'", "''") & "') AND IsLogin=" & IsLogin.Yes & " AND IsActive=" & IsActive.Active
        Try
            con = DBHelper.GetConnection()
            Dim lParamArray As SqlParameter() = Nothing
            lParamArray = New SqlParameter() { _
                New SqlParameter("@Username", _Username) _
            }
            reader = SqlHelper.ExecuteReader(con, CommandType.Text, sql, lParamArray)
            If reader.Read Then
                Dim strValue As New ArrayList()
                strValue = DBHelper.ReadValue(reader)
                Return GetEntity(0, strValue)
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
                con = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try

        Return Nothing
    End Function


    Public Shared Function GetEntity(ByRef indexStart As Integer, ByVal strValue As ArrayList) As User
        Dim obj As User = New User()
        obj.ID = strValue(indexStart)
        indexStart += 1
        obj.FullName = strValue(indexStart)
        indexStart += 1
        obj.UserName = strValue(indexStart)
        indexStart += 1
        obj.Password = strValue(indexStart)
        indexStart += 1
        obj.UserTypeID = strValue(indexStart)
        indexStart += 1
        obj.IsActive = strValue(indexStart)
        indexStart += 1
        obj.IsLogin = strValue(indexStart)
        indexStart += 1
        obj.CreatedDate = strValue(indexStart)
        indexStart += 1
        obj.ActivationCode = strValue(indexStart)
        indexStart += 1
        obj.ShippingAddress = strValue(indexStart)
        indexStart += 1
        Return obj
    End Function

    Public Shared Function findByID(ByVal _ID As String) As User
        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Dim sql As String = "SELECT * FROM " & _TableName & " " & _
                            "WHERE ID=@ID "
        Try
            con = DBHelper.GetConnection()
            Dim lParamArray As SqlParameter() = Nothing
            lParamArray = New SqlParameter() { _
                New SqlParameter("@ID", _ID) _
            }
            reader = SqlHelper.ExecuteReader(con, CommandType.Text, sql, lParamArray)
            If reader.Read Then
                Dim strValue As New ArrayList()
                strValue = DBHelper.ReadValue(reader)
                Return GetEntity(0, strValue)
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
                con = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try

        Return Nothing
    End Function
End Class
